CREATE PROCEDURE Contact_FindAll
AS
BEGIN
    Select 
    ContactID,
    FirstName,
    LastName,
    CompanyName,
    Address,
    MainPhone,
    Email,
    Subject,
    Body,
    DateCreated
    from Contact
END
GO

CREATE PROCEDURE Contact_FindById
@ContactID int
AS
BEGIN
    Select * from Contact where ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Contact where ContactID = @name
END
GO

CREATE PROCEDURE Contact_Insert
        @ContactID int ,
        @FirstName nvarchar (50),
        @LastName nvarchar (50),
        @CompanyName nvarchar (50),
        @Address nvarchar (50),
        @MainPhone char (20),
        @Email nvarchar (50),
        @Subject nvarchar (50),
        @Body nvarchar (-1),
        @DateCreated datetime 
AS
BEGIN
INSERT INTO Contact
(
        ContactID,
        FirstName,
        LastName,
        CompanyName,
        Address,
        MainPhone,
        Email,
        Subject,
        Body,
        DateCreated
)
VALUES
(
        @ContactID,
        @FirstName,
        @LastName,
        @CompanyName,
        @Address,
        @MainPhone,
        @Email,
        @Subject,
        @Body,
        @DateCreated
)
select @ContactID = @@IDENTITY
END
GO

CREATE PROCEDURE Contact_Update
        @ContactID int ,
        @FirstName nvarchar (50),  
        @LastName nvarchar (50),  
        @CompanyName nvarchar (50),  
        @Address nvarchar (50),  
        @MainPhone char (20),  
        @Email nvarchar (50),  
        @Subject nvarchar (50),  
        @Body nvarchar (-1),  
        @DateCreated datetime
AS
BEGIN
UPDATE  Contact SET
    FirstName = @FirstName,
    LastName = @LastName,
    CompanyName = @CompanyName,
    Address = @Address,
    MainPhone = @MainPhone,
    Email = @Email,
    Subject = @Subject,
    Body = @Body,
    DateCreated = @DateCreated
    WHERE ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_Delete
    @ContactID int
AS
BEGIN
    DELETE FROM Contact where ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(ContactID) FROM Contact;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Contact
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Contact_SetActive
@ContactID int
AS
declare @Type bit
SELECT @Type =  IsActive from Contact where ContactID = @ContactID
BEGIN
IF(@Type = 1)
Update Contact set IsActive = 0 where ContactID = @ContactID
else
Update Contact set IsActive = 1 where ContactID = @ContactID
END
GO

CREATE proc Contact_SetSortIndex
@ContactID int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Contact where ContactID = @ContactID
BEGIN
IF(@Type = 1)
Update Contact set SortIndex =  @CurrentSort + 1 where ContactID = @ContactID
else
Update Contact set SortIndex =  @CurrentSort - 1 where ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_RemoveSelectedItem
    @ContactID int
AS
BEGIN
    UPDATE Contact SET IsDeleted = 1 WHERE ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_RestoreSelectedItem
    @ContactID int
AS
BEGIN
    UPDATE Contact SET IsDeleted = 0 WHERE ContactID = @ContactID
END
GO

CREATE PROCEDURE Contact_RemoveAll
    @ContactID int
AS
BEGIN
    UPDATE Contact SET IsDeleted = 1
END
GO

CREATE PROCEDURE Contact_RestoreAll
    @ContactID int
AS
BEGIN
    UPDATE Contact SET IsDeleted = 0
END
GO

